﻿<!doctype html>
<html>
<head>
    <title>SpreadJS - Basic Conditional Format</title>
    <meta charset="utf-8" />
    <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1" />

    <link href="../../../external/spreadjs/css/gcspread.sheets.excel2013white.9.40.20161.0.css" rel="stylesheet" type="text/css" />

    <script src="../../../external/external/jquery-1.8.2.min.js" type="text/javascript"></script>

    <script type="text/javascript" src="../../../external/spreadjs/gcspread.sheets.all.9.40.20161.0.min.js"></script>

    <script id="scriptInit" type="text/javascript">
        /*code_begin*/
        var spreadNS = GcSpread.Sheets;
        $(document).ready(function () {
            var spread = new GcSpread.Sheets.Spread(document.getElementById("ss"), { sheetCount: 2 });
            initSpread(spread);
        });

        function initSpread(spread) {
            var sheet = spread.getSheet(0);
            sheet.isPaintSuspended(true);
            var cfs = sheet.getConditionalFormats();
            // sample title
            sheet.addSpan(1, 1, 1, 7);
            sheet.setValue(1, 1, "Conditional Format Samples");
            sheet.getCell(1, 1).font("24px sans-serif");
            sheet.getCell(1, 1).hAlign(spreadNS.HorizontalAlign.center);
            sheet.setRowHeight(1, 35);
            // cell value rule
            var r = 2;
            var c = 1;
            var w = 3;
            var h = 4;
            sheet.addSpan(r, c, 1, w);
            sheet.setValue(r, c, "Displays green background: value > 50:");
            sheet.getCell(r, c).wordWrap(true);
            sheet.setRowHeight(r, 40);
            var increase = 0;
            for (var row = 0; row < h - 1; row++) {
                for (var col = 0; col < w; col++) {
                    sheet.setValue(row + 3, col + 1, increase);
                    increase += 10;
                }
            }
            sheet.setBorder(new spreadNS.Range(r, c, h, w), new spreadNS.LineBorder("Black", spreadNS.LineStyle.dashed), {all: true});
            var cvRule = new spreadNS.CellValueRule();
            cvRule.ranges = [new spreadNS.Range(r + 1, c, h - 1, w)];
            cvRule.operator = spreadNS.ComparisonOperator.GreaterThan;
            cvRule.value1 = 50;
            var style = new spreadNS.Style();
            style.backColor = "#CCFFCC";
            cvRule.style = style;
            cfs.addRule(cvRule);
            r = 2;
            c = 5;
            sheet.addSpan(r, c, 1, w);
            sheet.setValue(r, c, "Displays red background: value >= 60 and value <= 70:");
            sheet.getCell(r, c).wordWrap(true);
            sheet.setRowHeight(r, 40);
            increase = 0;
            for (var row = 0; row < h - 1; row++) {
                for (var col = 0; col < w; col++) {
                    sheet.setValue(row + r + 1, col + c, increase);
                    increase += 10;
                }
            }
            sheet.setBorder(new spreadNS.Range(r, c, h, w), new spreadNS.LineBorder("Black", spreadNS.LineStyle.dashed), {all: true});
            cvRule = new spreadNS.CellValueRule();
            cvRule.ranges = [ new spreadNS.Range(r + 1, c, h - 1, w) ];
            cvRule.operator = spreadNS.ComparisonOperator.Between;
            cvRule.value1 = 60;
            cvRule.value2 = 70;
            style = new spreadNS.Style();
            style.backColor = "#FFCCCC";
            cvRule.style = style;
            cfs.addRule(cvRule);
            // 2 color scale rule
            r = 7;
            c = 1;
            sheet.addSpan(r, c, 1, w);
            sheet.setValue(r, c, "Displays two color gradient represents cell value:");
            sheet.getCell(r, c).wordWrap(true);
            sheet.setRowHeight(r, 40);
            increase = 0;
            for (var row = 0; row < h - 1; row++) {
                for (var col = 0; col < w; col++) {
                    sheet.setValue(row + r + 1, col + c, increase);
                    increase += 10;
                }
            }
            sheet.setBorder(new spreadNS.Range(r, c, h, w), new spreadNS.LineBorder("Black", spreadNS.LineStyle.dashed), {all: true});
            var tcsRule = new spreadNS.TwoScaleRule(spreadNS.ScaleValueType.LowestValue, null, "#FF9999", spreadNS.ScaleValueType.HighestValue, null, "#9999FF");
            tcsRule.ranges = [ new spreadNS.Range(r + 1, c, h - 1, w) ];
            cfs.addRule(tcsRule);
            // 3 color scale rule
            r = 7;
            c = 5;
            sheet.addSpan(r, c, 1, w);
            sheet.setValue(r, c, "Displays three color gradient represents cell value:");
            sheet.getCell(r, c).wordWrap(true);
            sheet.setRowHeight(r, 40);
            increase = 0;
            for (var row = 0; row < h - 1; row++) {
                for (var col = 0; col < w; col++) {
                    sheet.setValue(row + r + 1, col + c, increase);
                    increase += 10;
                }
            }
            sheet.setBorder(new spreadNS.Range(r, c, h, w), new spreadNS.LineBorder("Black", spreadNS.LineStyle.dashed), {all: true});
            var threecsRule = new spreadNS.ThreeScaleRule(spreadNS.ScaleValueType.LowestValue, null, "#FF9999",
                    spreadNS.ScaleValueType.Number, 100, "#99FF99",
                    spreadNS.ScaleValueType.HighestValue, null, "#9999FF");
            threecsRule.ranges = [new spreadNS.Range(r + 1, c, h - 1, w) ];
            cfs.addRule(threecsRule);
            // date occurring rule
            addDays = function (date, days) {
                var dt = new Date(date.getFullYear(), date.getMonth(), date.getDate() + days);
                if (days) {
                    if (dt.getDate() === date.getDate()) {
                        dt = new Date(date.getFullYear(), date.getMonth(), date.getDate());
                        dt.setTime(dt.getTime() + (days * 24 * 3600 * 1000));
                    }
                }
                return dt;
            };
            r = 12;
            c = 1;
            sheet.addSpan(r, c, 1, w);
            sheet.setValue(r, c, "Display blue background if cell value is in next week:");
            sheet.getCell(r, c).wordWrap(true);
            sheet.setRowHeight(r, 40);
            increase = 0;
            for (var row = 0; row < h - 1; row++) {
                for (var col = 0; col < w; col++) {
                    sheet.setValue(row + r + 1, col + c, addDays(new Date(), increase));
                    sheet.setFormatter(row + r + 1, col + c, "yyyy/mm/dd", spreadNS.SheetArea.viewport);
                    increase += 1;
                }
            }
            sheet.setBorder(new spreadNS.Range(r, c, h, w), new spreadNS.LineBorder("Black", spreadNS.LineStyle.dashed), {all: true});
            var doRule = new spreadNS.DateOccurringRule();
            doRule.ranges = [ new spreadNS.Range(r + 1, c, h - 1, w) ];
            doRule.type = spreadNS.DateOccurringType.NextWeek;
            style = new spreadNS.Style();
            style.backColor = "#CCCCFF";
            doRule.style = style;
            cfs.addRule(doRule);

            // specific text rule
            r = 12;
            c = 5;
            sheet.addSpan(r, c, 1, w);
            sheet.setValue(r, c, "Display red foreground if cell value contains \"o\":");
            sheet.getCell(r, c).wordWrap(true);
            sheet.setRowHeight(r, 40);
            var data = ["The", "quick", "brown", "fox", "jumps", "over", "the", "lazy", "dog", "The", "quick", "brown", "fox", "jumps", "over", "the", "lazy", "dog"];
            increase = 0;
            for (var row = 0; row < h - 1; row++) {
                for (var col = 0; col < w; col++) {
                    sheet.setValue(row + r + 1, col + c, data[increase]);
                    increase += 1;
                }
            }
            sheet.setBorder(new spreadNS.Range(r, c, h, w), new spreadNS.LineBorder("Black", spreadNS.LineStyle.dashed), {all: true});
            var stRule = new spreadNS.SpecificTextRule();
            stRule.ranges = [ new spreadNS.Range(r + 1, c, h - 1, w) ];
            stRule.operator = spreadNS.TextComparisonOperator.Contains;
            stRule.text = "o";
            var style = new spreadNS.Style();
            style.foreColor = "Red";
            style.font = "bold 12px sans-serif";
            stRule.style = style;
            cfs.addRule(stRule);

            // data bar rule
            r = 17;
            c = 1;
            sheet.addSpan(r, c, 1, w);
            sheet.setValue(r, c, "Display a colored data bar represents cell value:");
            sheet.getCell(r, c).wordWrap(true);
            sheet.setRowHeight(r, 40);
            increase = 0;
            for (var row = 0; row < h - 1; row++) {
                for (var col = 0; col < w; col++) {
                    sheet.setValue(row + r + 1, col + c, increase);
                    increase += 10;
                }
            }
            sheet.setBorder(new spreadNS.Range(r, c, h, w), new spreadNS.LineBorder("Black", spreadNS.LineStyle.dashed), { all: true });
            var dataBarRule = new spreadNS.DataBarRule();
            dataBarRule.ranges = [new spreadNS.Range(r + 1, c, h - 1, w)];
            dataBarRule.minimumType(spreadNS.ScaleValueType.LowestValue);
            dataBarRule.maximumType(spreadNS.ScaleValueType.HighestValue);
            dataBarRule.gradient(true);
            dataBarRule.color("green");
            dataBarRule.showBorder(false);
            dataBarRule.showBarOnly(false);
            cfs.addRule(dataBarRule);


            // icon set rule
            r = 17;
            c = 5;
            sheet.addSpan(r, c, 1, w);
            sheet.setValue(r, c, "Displays an icon represents cell value:");
            sheet.getCell(r, c).wordWrap(true);
            sheet.setRowHeight(r, 40);
            increase = 0;
            for (var row = 0; row < h - 1; row++) {
                for (var col = 0; col < w; col++) {
                    sheet.setValue(row + r + 1, col + c, increase);
                    increase += 10;
                }
            }
            sheet.setBorder(new spreadNS.Range(r, c, h, w), new spreadNS.LineBorder("Black", spreadNS.LineStyle.dashed), { all: true });
            var iconSetRule = new spreadNS.IconSetRule();
            iconSetRule.ranges = [new spreadNS.Range(r + 1, c, h - 1, w)];
            iconSetRule.iconSetType(spreadNS.IconSetType.FiveArrowsColored);
            iconSetRule.reverseIconOrder(false);
            iconSetRule.showIconOnly(false);
            cfs.addRule(iconSetRule);

            sheet.isPaintSuspended(false);
            updateRuleCount();
            $("#removeRule").click(function() {
                var sels = sheet.getSelections();
                if (sels && sels.length > 0) {
                    var sel = sels[0];
                    cfs.removeRuleByRange(sel.row, sel.col, sel.rowCount, sel.colCount);
                    updateRuleCount();
                }
            });
            $("#clearRule").click(function() {
                cfs.clearRule();
                updateRuleCount();
            });

            function updateRuleCount() {
                $("#ruleCount").text("Rule Count: " + cfs.count());
            }
        };
        function getActualRange(range, maxRowCount, maxColCount) {
            var row = range.row < 0 ? 0 : range.row;
            var col = range.col < 0 ? 0 : range.col;
            var rowCount = range.rowCount < 0 ? maxRowCount : range.rowCount;
            var colCount = range.colCount < 0 ? maxColCount : range.colCount;

            return new spreadNS.Range(row, col, rowCount, colCount);
        }
        /*code_end*/
    </script>

</head>
<body>
<div class="sample-turtorial">
    <div id="ss" style="width:100%; height:580px;border: 1px solid gray;"></div>
    <div class="demo-options">
        <div class="option-row">
            <label id="ruleCount"></label>
        </div>
        <div class="option-row">
            <input type="button" id="removeRule" value="Remove the selection's rule" />
            <input type="button" id="clearRule" value="Remove all the rules">
        </div>
    </div>
</div>
</body>
</html>
